﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Business.Compta;
using System.Configuration;
using System.Data.OleDb;
using DAO;

namespace DAO.Compta
{
    public class ComptaUsersDAO
    {
        private static string ID = MainDAO.ID;
        private static string USERS = MainDAO.USERS;
        private static string USERS_ACCOUNT = MainDAO.USERS_ACCOUNT;
        private static string ID_ACCOUNT = MainDAO.ID_ACCOUNT;
        private static string ID_CATEG = MainDAO.ID_CATEG;
        private static string ID_USER = MainDAO.ID_USER;
        private static string CATEGORIES_PER_USER = MainDAO.CATEGORIES_PER_USER;
        private static string PERCENT = MainDAO.PERCENT;


        #region "Users"
        public static List<User> getAllUsers()
        {
            List<User> _return = new List<User>();

            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT * from "+USERS, conn);
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id;
            string login, password, userName, mail;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                login = (string)myOleDbDataReader.GetValue(1);
                password = (string)myOleDbDataReader.GetValue(2);
                userName = (string)myOleDbDataReader.GetValue(3);
                mail = (string)myOleDbDataReader.GetValue(4);
                _return.Add(new User(id, login, userName, password, mail));

            }
            myOleDbDataReader.Close();
            conn.Close();
            return _return;
        }

        public static List<User> getOwners(int idAccount)
        {
            List<User> _return = new List<User>();
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT * from " + USERS + " u, " + USERS_ACCOUNT +
                " ua where u." + ID + " = ua." + ID_USER + " and ua." + ID_ACCOUNT + " = @idAccount", conn);
            myOleDbCommand2.Parameters.Add("@idAccount", OleDbType.Integer).Value = idAccount;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            int id;
            string login, password, userName, mail;

            while (myOleDbDataReader.Read())
            {
                id = myOleDbDataReader.GetInt32(0);
                login = (string)myOleDbDataReader.GetValue(1);
                password = (string)myOleDbDataReader.GetValue(2);
                userName = (string)myOleDbDataReader.GetValue(3);
                mail = (string)myOleDbDataReader.GetValue(4);
                _return.Add(new User(id, login, userName, password, mail));
            }
            _return.Add(new User(-1, null, null, null, null));
            myOleDbDataReader.Close();
            conn.Close();

            return _return;
        }



        public static void ModifyUser(User line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update USERS set ULOGIN = @login, UPWD = @password, UNAME = @userName, UMAIL = @mail where ID = @id";
            myCommand.Parameters.Add("@login", OleDbType.VarChar).Value = line.Login;
            myCommand.Parameters.Add("@password", OleDbType.VarChar).Value = line.Password;
            myCommand.Parameters.Add("@userName", OleDbType.VarChar).Value = line.UserName;
            myCommand.Parameters.Add("@mail", OleDbType.VarChar).Value = line.Mail;
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static int InserNewUser(User line)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.CommandText = "insert into USERS(ULOGIN, UPWD, UNAME, UMAIL) VALUES (@login,@password,@userName, @mail)";
            myCommand.Parameters.Add("@login", OleDbType.VarChar).Value = line.Login;
            myCommand.Parameters.Add("@password", OleDbType.VarChar).Value = "123456";
            myCommand.Parameters.Add("@userName", OleDbType.VarChar).Value = line.UserName;
            myCommand.Parameters.Add("@mail", OleDbType.VarChar).Value = line.Mail;

            OleDbTransaction trans = conn.BeginTransaction();
            myCommand.Transaction = trans;
            myCommand.Connection = conn;
            myCommand.ExecuteNonQuery();
            OleDbCommand myOleDbCommand2;
            myOleDbCommand2 = new OleDbCommand("SELECT @@IDENTITY", conn);
            myOleDbCommand2.Transaction = trans;
            OleDbDataReader myOleDbDataReader = myOleDbCommand2.ExecuteReader();
            myOleDbDataReader.Read();

            int executeInsertGetIdentity = (int)myOleDbDataReader.GetValue(0);
            trans.Commit();
            myOleDbDataReader.Close();
            conn.Close();
            return executeInsertGetIdentity;
        }

        public static int deleteUser(User line)
        {
            int _return;
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            //// Read in the file
            OleDbCommand myCommand = new OleDbCommand();

            myCommand.CommandText = "delete from USERS where ID = @id";
            myCommand.Parameters.Add("@id", OleDbType.Integer).Value = line.Id;
            myCommand.Connection = conn;
            _return = myCommand.ExecuteNonQuery();
            conn.Close();
            return _return;
        }
        #endregion


        #region Layout
        public static void ModifyLayout(System.IO.MemoryStream layout, int idUser)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();

            OleDbCommand myCommand = new OleDbCommand();
            myCommand.Connection = conn;
            myCommand.CommandText = "update USERS set LAYOUT = @layout where ID = @idUser";

            myCommand.Parameters.Add("@layout", OleDbType.Binary).Value = layout.ToArray();
            myCommand.Parameters.Add("@idUser", OleDbType.Integer).Value = idUser;

            myCommand.ExecuteNonQuery();
            conn.Close();
        }

        public static byte[] getLayout(int idUser)
        {
            OleDbConnection conn = MainDAO.OpenComptaConnection();
            OleDbCommand myOleDbCommand2 = new OleDbCommand("select LAYOUT FROM USERS where ID = @idUser",
                conn);
            myOleDbCommand2.Parameters.Add("@idUser", OleDbType.Integer).Value = idUser;

            OleDbDataReader dr = myOleDbCommand2.ExecuteReader();

            while (dr.Read())
            {
                if (dr.IsDBNull(0))
                    return null;
                else
                {
                    byte[] layout = (byte[])dr.GetValue(0);
                    if (layout.Length > 0)
                        return layout;
                    else
                        return null;
                }
            }
            dr.Close();
            conn.Close();
            return null;
        }
        #endregion
    }
}
